package private

import (
	"dc-go/global"
	"dc-go/model"
	"dc-go/utils"
	"fmt"

	"github.com/gin-gonic/gin"
	"github.com/xuri/excelize/v2"
	"go.uber.org/zap"
)

type ApiSql struct{}

func (a *ApiSql) GetSqlList(c *gin.Context) {
	var requestSqlList model.RequestGetSqlList
	if err := model.RequestShouldBindJSON(c, &requestSqlList); err != nil {
		return
	}
	if sqlList, total, err := servicePrivate.ServiceSql.GetSqlList(requestSqlList); err != nil {
		global.DcLogger.Error("获取Sql列表失败! ", zap.Any("err", err))
		model.ResponseErrorMessage("获取Sql列表失败, "+err.Error(), c)
	} else {
		model.ResponseSuccessData(model.ResponsePage{
			Records:  sqlList,
			Page:     requestSqlList.Page,
			PageSize: requestSqlList.PageSize,
			Total:    total,
		}, c)
	}
}

func (a *ApiSql) EditSql(c *gin.Context) {
	var toEditSql model.SysSql
	if err := model.RequestShouldBindJSON(c, &toEditSql); err != nil {
		return
	}
	toEditSql.UpdatedBy = utils.GetEmpeeAcct(c)
	if err := servicePrivate.ServiceSql.EditSql(toEditSql, utils.GetEmpeeAcct(c)); err != nil {
		global.DcLogger.Error("编辑Sql失败! ", zap.Any("err", err))
		model.ResponseErrorMessage("编辑Sql失败, "+err.Error(), c)
	} else {
		global.DcLogger.Warn(utils.GetEmpeeAcct(c) + "编辑Sql成功! ")
		model.ResponseSuccessMessage("编辑Sql成功! ", c)
	}
}

func (a *ApiSql) AddSql(c *gin.Context) {
	var toAddSql model.RequestAddSql
	if err := model.RequestShouldBindJSON(c, &toAddSql); err != nil {
		return
	}
	memo := toAddSql.Memo
	addSql := &model.SysSql{
		DcModel: model.DcModel{
			CreatedBy: utils.GetEmpeeAcct(c),
			UpdatedBy: utils.GetEmpeeAcct(c),
			Status:    toAddSql.Status,
			Sort:      toAddSql.Sort,
			Memo:      &memo,
		},
		SqlCode:    toAddSql.SqlCode,
		SqlType:    toAddSql.SqlType,
		SchemaName: toAddSql.SchemaName,
		TableName:  toAddSql.TableName,
		HasDayId:   toAddSql.HasDayId,
		SqlString:  toAddSql.SqlString,
	}
	if err := servicePrivate.ServiceSql.AddSql(*addSql, utils.GetEmpeeAcct(c)); err != nil {
		global.DcLogger.Error("添加Sql失败! ", zap.Any("err", err))
		model.ResponseErrorMessage("添加Sql失败, "+err.Error(), c)
	} else {
		model.ResponseSuccessMessage("添加Sql成功! ", c)
	}
}

func (a *ApiSql) ValidateSql(c *gin.Context) {
	var toTestSql model.RequestValidateSql
	if err := model.RequestShouldBindJSON(c, &toTestSql); err != nil {
		return
	}
	if _, _, err := servicePrivate.ServiceSql.ValidateSql(toTestSql.SqlString, utils.GetEmpeeAcct(c)); err != nil {
		global.DcLogger.Error("测试Sql失败! ", zap.Any("err", err))
		model.ResponseErrorMessage("测试Sql失败, "+err.Error(), c)
	} else {
		model.ResponseSuccessMessage("测试Sql成功! ", c)
	}
}

func (a *ApiSql) ValidatePreDefinedTable(c *gin.Context) {
	var toValidateTable model.RequestValidatePreDefinedTable
	if err := model.RequestShouldBindJSON(c, &toValidateTable); err != nil {
		return
	}
	if err := servicePrivate.ServiceSql.ValidatePreDefinedTable(toValidateTable.SqlType, toValidateTable.HasDayId, toValidateTable.SchemaName, toValidateTable.TableName); err != nil {
		global.DcLogger.Error("测试Sql失败! ", zap.Any("err", err))
		model.ResponseErrorMessage("测试Sql失败, "+err.Error(), c)
	} else {
		model.ResponseSuccessMessage("测试Sql成功! ", c)
	}
}

func (a *ApiSql) QuerySqlById(c *gin.Context) {
	var toQueryId model.RequestQueryById
	if err := model.RequestShouldBindJSON(c, &toQueryId); err != nil {
		return
	}
	if sql, err := servicePrivate.ServiceSql.QuerySqlById(toQueryId.Id); err != nil {
		global.DcLogger.Error("查找Sql失败! ", zap.Any("err", err))
		model.ResponseErrorMessage("查找Sql失败, "+err.Error(), c)
	} else {
		model.ResponseSuccessMessageData(gin.H{"records": sql}, "查找Sql成功! ", c)
	}
}

func (a *ApiSql) QuerySqlBySqlCode(c *gin.Context) {
	var toQuerySqlCode model.RequestQueryBySqlCode
	if err := model.RequestShouldBindJSON(c, &toQuerySqlCode); err != nil {
		return
	}
	if sql, err := servicePrivate.ServiceSql.QuerySqlBySqlCode(toQuerySqlCode.SqlCode); err != nil {
		global.DcLogger.Error("查找Sql失败! ", zap.Any("err", err))
		model.ResponseErrorMessage("查找Sql失败, "+err.Error(), c)
	} else {
		model.ResponseSuccessMessageData(gin.H{"records": sql}, "查找Sql成功! ", c)
	}
}

func (a *ApiSql) DeleteSqlById(c *gin.Context) {
	var toDeleteId model.RequestQueryById
	if err := model.RequestShouldBindJSON(c, &toDeleteId); err != nil {
		return
	}
	if err := servicePrivate.ServiceSql.DeleteSqlById(toDeleteId.Id); err != nil {
		global.DcLogger.Error("删除Sql失败! ", zap.Any("err", err))
		model.ResponseErrorMessage("删除Sql失败, "+err.Error(), c)
	} else {
		global.DcLogger.Warn(utils.GetEmpeeAcct(c) + "删除Sql成功! ")
		model.ResponseSuccessMessage("删除Sql成功! ", c)
	}
}

func (a *ApiSql) EditSqlSqlparam(c *gin.Context) {
	var sqlSqlparam model.RequestSqlSqlparamEdit
	if err := model.RequestShouldBindJSON(c, &sqlSqlparam); err != nil {
		return
	}

	if err := servicePrivate.ServiceSql.EditSqlSqlparam(&sqlSqlparam); err != nil {
		global.DcLogger.Error("编辑Sql和参数关联失败！", zap.Any("err", err))
		model.ResponseErrorMessage("编辑Sql和参数关联失败，"+err.Error(), c)
	} else {
		global.DcLogger.Warn(utils.GetEmpeeAcct(c) + "编辑Sql和参数关联成功！")
		model.ResponseSuccessMessage("编辑Sql和参数关联成功！", c)
	}
}

func (a *ApiSql) QuerySqlResultBySqlCode(c *gin.Context) {
	var toQuerySqlCode model.RequestQueryBySqlCodeAndParams
	if err := model.RequestShouldBindJSON(c, &toQuerySqlCode); err != nil {
		return
	}
	if sqlres, total, err := servicePrivate.ServiceSql.QuerySqlResultBySqlCode(toQuerySqlCode, utils.GetEmpeeAcct(c)); err != nil {
		global.DcLogger.Error("查找Sql结果失败! ", zap.Any("err", err))
		model.ResponseErrorMessage("查找Sql结果失败, "+err.Error(), c)
	} else {
		model.ResponseSuccessData(model.ResponsePage{
			Records: sqlres,
			Total:   total,
		}, c)
	}
}

func (a *ApiSql) QuerySqlColumnsBySqlCode(c *gin.Context) {
	var toQuerySqlCode model.RequestQueryBySqlCode
	if err := model.RequestShouldBindJSON(c, &toQuerySqlCode); err != nil {
		return
	}
	if sqlcol, total, err := servicePrivate.ServiceSql.QuerySqlColumnsBySqlCode(toQuerySqlCode.SqlCode); err != nil {
		global.DcLogger.Error("查找Sql列信息失败! ", zap.Any("err", err))
		model.ResponseErrorMessage("查找Sql列信息失败, "+err.Error(), c)
	} else {
		model.ResponseSuccessData(model.ResponsePage{
			Records: sqlcol,
			Total:   total,
		}, c)
	}
}

func (a *ApiSql) QueryDirectSql(c *gin.Context) {
	var toQuerySql model.RequestQueryDirectSql
	if err := model.RequestShouldBindJSON(c, &toQuerySql); err != nil {
		return
	}
	if _, sqlres, total, err := servicePrivate.ServiceSql.QueryDirectSql(toQuerySql.Sql); err != nil {
		global.DcLogger.Error("查询Sql结果失败! ", zap.Any("err", err))
		model.ResponseErrorMessage("查询Sql结果失败, "+err.Error(), c)
	} else {
		model.ResponseSuccessData(model.ResponsePage{
			Records: sqlres,
			Total:   total,
		}, c)
	}
}

func (a *ApiSql) SaveDirectSqlResultToExcel(c *gin.Context) {
	var toQuerySql model.RequestQueryDirectSql
	if err := model.RequestShouldBindJSON(c, &toQuerySql); err != nil {
		return
	}
	// if file, err := servicePrivate.ServiceSql.SaveDirectSqlResultToExcel(toQuerySql.Sql); err != nil {
	// 	global.DcLogger.Error("查询Sql结果失败! ", zap.Any("err", err))
	// 	model.ResponseErrorMessage("查询Sql结果失败, "+err.Error(), c)
	// } else {
	// 	c.Writer.Header().Add("Content-Disposition", fmt.Sprintf("attachment; filename=%s", file.Path))
	// 	c.Writer.Header().Add("Content-Type", "application/msexcel")
	// 	file.Write(c.Writer)
	// }

	if sqlCols, sqlres, _, err := servicePrivate.ServiceSql.QueryDirectSql(toQuerySql.Sql); err != nil {
		global.DcLogger.Error("查询Sql结果失败! ", zap.Any("err", err))
		model.ResponseErrorMessage("查询Sql结果失败, "+err.Error(), c)
	} else {
		f := excelize.NewFile()
		// fmt.Printf("%#v\n", sqlres)
		// 判断查询结果是否为空
		if len(sqlres) == 0 {
			model.ResponseErrorMessage("查询结果为空", c)
			return
		}
		// 设置表头
		for i, v := range sqlCols {
			colName, err := excelize.ColumnNumberToName(v)
			if err != nil {
				global.DcLogger.Error("查询Sql结果失败! ", zap.Any("err", err))
				model.ResponseErrorMessage("查询Sql结果失败, "+err.Error(), c)
			}
			f.SetCellValue("Sheet1", fmt.Sprintf("%s%d", colName, 1), i)
		}

		// 设置表内容
		for i, v := range sqlres {
			for j, k := range v {
				colName, err := excelize.ColumnNumberToName(sqlCols[j])
				if err != nil {
					global.DcLogger.Error("查询Sql结果失败! ", zap.Any("err", err))
					model.ResponseErrorMessage("查询Sql结果失败, "+err.Error(), c)
				}
				f.SetCellValue("Sheet1", fmt.Sprintf("%s%d", colName, i+2), k)
			}
		}
		_, err := f.NewSheet("Sheet2")
		if err != nil {
			fmt.Println(err)
			return
		}
		f.SetCellValue("Sheet2", "A1", "SQL: ")
		f.SetCellValue("Sheet2", "A2", toQuerySql.Sql)
		f.Path = "Book1.xlsx"
		c.Writer.Header().Set("Content-Disposition", fmt.Sprintf("attachment; filename=%s", f.Path))
		c.Writer.Header().Set("Content-Type", "application/msexcel")
		if err := f.Write(c.Writer); err != nil {
			fmt.Fprint(c.Writer, err.Error())
		}
	}
}
